/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package BUS;

import DAL.ConnectUnit;
import DTO.CustomerDTO;
import DTO.SavingBookDTO;
import DTO.SavingTypeDTO;
import java.sql.CallableStatement;
import java.sql.Date;
import javax.swing.JOptionPane;

/**
 *
 * @author Mr.Sam
 */
public class SavingBookBUS {
    public ConnectUnit conn;
    public SavingBookBUS()
    {
        conn=DAL.DAL.getDAL();
    }
    public boolean Insert(String procedure, SavingBookDTO info_savingbook,CustomerDTO info_customer, SavingTypeDTO info_savingtype) throws Exception
    {
        CallableStatement cs=conn.GetCallableStatement(procedure);
        cs.setString(1, info_savingbook.getBookID());
        cs.setString(2, info_customer.getFullName());
        cs.setString(3, info_customer.getIDNumber());
        cs.setInt(4, info_savingtype.getTerm());
        cs.setDate(5, (Date) info_savingbook.getOpeningDate());
        cs.setFloat(6, info_savingbook.getAccountBalance());
        return cs.execute();
        //return conn.Insert("Customer", customer);
    }
  
    public String Search(
            boolean bFullName,String FullName,
            boolean bIDNumber,String IDNumber,
            boolean bSBStatus,String SBStatus,
            boolean bTypeName,String TypeName,
            boolean bOpeningDate,String From_OpeningDate,String To_OpeningDate,
            boolean bAccountBalance, float From_AccountBalance,float To_AccountBalance,
            boolean bInterestRate,float From_InterestRate,float To_InterestRate,
            boolean bTerm,int from_Term,int To_Term
            )
    {
        String sql="select BookID,FullName,IDNumber,TypeName,InterestRate,AccountBalance,OpeningDate from (customer inner join savingbook on customer.customerid=savingbook.customerid) inner join savingtype on savingtype.typeid=savingbook.typeid";
        boolean isFirst=true;
        if(bFullName)
        {
            sql+="where FullName like '%"+FullName+"%' ";
            isFirst=false;
        }
        if(bIDNumber)
        {
             if (isFirst)
                {
                    sql += "where IDNumber like '%" + IDNumber + "%' ";
                    isFirst = false;
                }
             else
             {
                 sql+="and IDNumber like '%" + IDNumber + "%' ";
             }
        }
        if(bSBStatus)
        {
             if (isFirst)
                {
                    sql += "where SBStatus=" + SBStatus + " ";
                    isFirst = false;
                }
             else
             {
                 sql+="and SBStatus=" + SBStatus + " ";
             }
        }
        if(bTypeName)
        {
             if (isFirst)
                {
                    sql += "where TypeName like '%" + TypeName + "%' ";
                    isFirst = false;
                }
             else
             {
                 sql+="and TypeName like '%" + TypeName + "%' ";
             }
        }
        if(bOpeningDate)
        {
             if (isFirst)
                {
                    sql += "where OpeningDate between '" + From_OpeningDate + "' and '"+To_OpeningDate+"' ";
                    isFirst = false;
                }
             else
             {
                 sql+="and OpeningDate between '" + From_OpeningDate + "' and '"+To_OpeningDate+"' ";
             }
        }
        if(bAccountBalance)
        {
             if (isFirst)
                {
                    sql += "where AccountBalance between '" + From_AccountBalance + "' and '"+To_AccountBalance+"' ";
                    isFirst = false;
                }
             else
             {
                 sql+="and AccountBalance between '" + From_AccountBalance + "' and '"+To_AccountBalance+"' ";
             }
        }
        if(bInterestRate)
        {
             if (isFirst)
                {
                    sql += "where InterestRate between '" + From_InterestRate + "' and '"+To_InterestRate+"' ";
                    isFirst = false;
                }
             else
             {
                 sql+="and InterestRate between '" + From_InterestRate + "' and '"+To_InterestRate+"' ";
             }
        }
        if(bTerm)
        {
             if (isFirst)
                {
                    sql += "where Term between '" + from_Term + "' and '"+To_Term+"' ";
                    isFirst = false;
                }
             else
             {
                 sql+="and Term between '" + from_Term + "' and '"+To_Term+"' ";
             }
        }
        return sql;
    }
    
    public static void main(String[] str) throws Exception
    {
        SavingBookBUS bus=new SavingBookBUS();
        CustomerDTO info_customer=new CustomerDTO();
        SavingBookDTO info_savingbook=new SavingBookDTO();
        SavingTypeDTO info_savingtype=new SavingTypeDTO();
        info_customer.setFullName("sa");
        info_customer.setIDNumber("123");
        info_savingtype.setTerm(1);
        info_savingbook.setAccountBalance(123);
        info_savingbook.setOpeningDate(new Date(2010,2, 10));
        info_savingbook.setBookID("");
        JOptionPane.showMessageDialog(null, bus.Insert("{call dbo.Insert_SavingBook(?,?,?,?,?,?)}", info_savingbook, info_customer, info_savingtype));
    }
}
